<?php 
/**
 *  Por Federico Pisarello - SisDev Software (c) 2012 - Buenos Aires, Argentina.
 * 	fpisarello@sisdevsoft.com
 */
 
require("includes/funciones.inc.php");

session_start();
if(!isset($_SESSION['userName']))
	exit();

function searchActiveTables()
{
	require("includes/constants.php");
	require("includes/datatables_db/Datatables.php");
	
	$datatables = new Datatables();
	
	$strSQL = "SELECT LEFT(dateFrom, 10) AS dateFrom, t.description, COUNT(*) AS countTables FROM orderfood of
				INNER JOIN tables t ON of.idTable = t.idTable 
				WHERE of.dateFrom >= '".empaquetarFecha($_REQUEST['dateFrom'])."' AND
					  of.dateFrom <= '".empaquetarFecha($_REQUEST['dateTo'])."' AND 
					  of.state = '$stateTableClose' ";

	if($_SESSION['userType'] == $idTypeAdministrator)
		$strSQL .= " AND of.idRestaurant = ".$_REQUEST['cmbRestaurant'];
	else
		$strSQL .= " AND of.idRestaurant = ".$_SESSION['userRestaurant'];
		
	$strSQL .= " GROUP BY LEFT(dateFrom,10), t.description";
	
	$datatables
		->select('dateFrom, description, countTables')
		->from('('.$strSQL.') activeTables');
		
	return $datatables->generate();
}

function searchRankingTables()
{
	require("includes/constants.php");
	require("includes/datatables_db/Datatables.php");
	
	$datatables = new Datatables();
	
	$strSQL = "SELECT LEFT(dateFrom, 10) AS dateFrom, t.description, SUM(quantity * price) AS sumPrice FROM orderfooditem oi
				INNER JOIN orderfood of ON of.idOrderFood = oi.idOrderFood
				INNER JOIN tables t ON of.idTable = t.idTable
				WHERE of.dateFrom >= '".empaquetarFecha($_REQUEST['dateFrom'])."' AND
					  of.dateFrom <= '".empaquetarFecha($_REQUEST['dateTo'])."' AND 
					  of.state = '$stateTableClose' ";

	if($_SESSION['userType'] == $idTypeAdministrator)
		$strSQL .= " AND of.idRestaurant = ".$_REQUEST['cmbRestaurant'];
	else
		$strSQL .= " AND of.idRestaurant = ".$_SESSION['userRestaurant'];
		
	$strSQL .= " GROUP BY LEFT(dateFrom, 10), t.description
				 ORDER BY sumPrice DESC";
	
	$datatables
		->select('dateFrom, description, sumPrice')
		->from('('.$strSQL.') rankingTables');
		
	return $datatables->generate();
}

function searchRankingFoods()
{
	require("includes/constants.php");
	require("includes/datatables_db/Datatables.php");
	
	$datatables = new Datatables();
	
	$strSQL = "SELECT LEFT(dateFrom, 10) AS dateFrom, f.description, SUM(quantity) AS countFood FROM orderfooditem oi
				INNER JOIN orderfood of ON of.idOrderFood = oi.idOrderFood
				INNER JOIN food f ON oi.idFood = f.idFood
				WHERE of.dateFrom >= '".empaquetarFecha($_REQUEST['dateFrom'])."' AND
					  of.dateFrom <= '".empaquetarFecha($_REQUEST['dateTo'])."' AND 
					  of.state = '$stateTableClose' ";

	if($_SESSION['userType'] == $idTypeAdministrator)
		$strSQL .= " AND of.idRestaurant = ".$_REQUEST['cmbRestaurant'];
	else
		$strSQL .= " AND of.idRestaurant = ".$_SESSION['userRestaurant'];
		
	$strSQL .= " GROUP BY LEFT(dateFrom, 10), f.description
				 ORDER BY countFood DESC";
	
	$datatables
		->select('dateFrom, description, countFood')
		->from('('.$strSQL.') rankingTables');
	
	return $datatables->generate();
}

function searchTimeTables()
{
	require("includes/constants.php");
	require("includes/datatables_db/Datatables.php");
	
	$datatables = new Datatables();
	
	$strSQL = "SELECT LEFT(dateFrom, 10) AS dateFrom, t.description, MIN(TIMEDIFF(dateTo, dateFrom)) AS minTimeTable, MAX(TIMEDIFF(dateTo, dateFrom)) AS maxTimeTable FROM orderfood of
				INNER JOIN tables t ON of.idTable = t.idTable
				WHERE of.dateFrom >= '".empaquetarFecha($_REQUEST['dateFrom'])."' AND
					  of.dateFrom <= '".empaquetarFecha($_REQUEST['dateTo'])."' AND 
					  of.state = '$stateTableClose' ";

	if($_SESSION['userType'] == $idTypeAdministrator)
		$strSQL .= " AND of.idRestaurant = ".$_REQUEST['cmbRestaurant'];
	else
		$strSQL .= " AND of.idRestaurant = ".$_SESSION['userRestaurant'];
		
	$strSQL .= " GROUP BY LEFT(dateFrom, 10), t.description
				 ORDER BY t.description DESC";
	
	$datatables
		->select('dateFrom, description, minTimeTable, maxTimeTable')
		->from('('.$strSQL.') rankingTables');
		
	return $datatables->generate();
}

function getRestaurants()
{
	require("includes/selectLanguage.php");

	$DB = NewADOConnection('mysqlt');
	$DB->Connect();
	$DB->Execute("SET NAMES utf8;");
	//$DB->debug=true;
	
	$rsRestaurant = $DB->Execute("SELECT * FROM restaurant");
	
	$i=0;
	while(!$rsRestaurant->EOF){
		$arrRestaurant->data[$i]['idRestaurant'] = $rsRestaurant->fields(0);
		$arrRestaurant->data[$i++]['businessName'] = $rsRestaurant->fields(1);
		$rsRestaurant->MoveNext();
	}
	
	return json_encode($arrRestaurant->data);
}

switch($_REQUEST['actionOfForm'])
{
	case "searchActiveTables":
		echo searchActiveTables();
		break;
		
	case "searchRankingTables":
		echo searchRankingTables();
		break;
		
	case "searchRankingFoods":
		echo searchRankingFoods();
		break;
		
	case "searchTimeTables":
		echo searchTimeTables();
		break;
		
	case "getRestaurants":
		echo getRestaurants();
		break;
}